﻿using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
namespace WPFExcelReport
{
    public class CaseInsensitiveComparer : IComparer<object>
    {
        public int Compare(object x, object y)
        {
            return string.Compare((string)x, (string)y, StringComparison.OrdinalIgnoreCase);
        }
    }
    class YCAAB05DOCReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string WBBTP;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string strExcelFile;
        string Today
        {
            get
            {
                DateTime now = DateTime.Now;
                return now.Year.ToString() + Right("00" + now.Month.ToString(), 2) + Right("00" + now.Day.ToString(), 2);
            }
        }
        #endregion
        public YCAAB05DOCReport()
            : base()
        {
        }
        public YCAAB05DOCReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                strExcelFile = @"YCA\YCAAB05DOC.xls";
                DataRow dwVKPOS = dsSource.Tables["ABKOPF"].Rows[0];
                string KRS = dwVKPOS["ABKAUFKRS"].ToString().Trim();
                string LFD = dwVKPOS["ABKAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string newfilename = KRS.Trim() + flfd.ToString();
                exAgent.CheckWorkBook(newfilename);
                LocalFile(KRS + LFD);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInEXCEL()
        {
            try
            {
                string value = "";
                DataRow row = null;
                Dictionary<string, int> colorpos = new Dictionary<string, int>();
                int x = 1, y = 1;
                x = 5; y = 14;
                row = dsSource.Tables["ABKOPF"].Rows[0];
                value = row["ABKAUFKRS"].ToString() + "-" + row["ABKAUFLFD"].ToString();
                exAgent.SetCellValue(x, y, value);

                x = 3; y = 30;
                exAgent.SetCellValue(x, y, Today);
                if (dsSource.Tables.Contains("LIEFDAT"))
                {
                    if (dsSource.Tables["LIEFDAT"].Rows.Count > 0)
                    {
                        row = dsSource.Tables["LIEFDAT"].Rows[0];
                        value = row["LSNAME1"].ToString().Trim() + row["LSNAME2"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 4;
                        exAgent.SetCellValue(x, y, value);
                        value = row["LSSTRASSE1"].ToString();
                        value = GetGB2312Value(value);
                        x = 4; y = 13;
                        exAgent.SetCellValue(x, y, value);
                    }
                }
                int rown = -1;
                string processed = "";
                foreach (DataRow dwABPOS in dsSource.Tables["ABPOS"].Rows)
                {
                    string art = dwABPOS["ABPART"].ToString();
                    string var = dwABPOS["ABPVAR"].ToString();
                    var = Left(var, 2);
                    string color = dwABPOS["ABPFARBE"].ToString();
                    string size = dwABPOS["ABPGROE"].ToString();
                    double qty = System.Convert.ToDouble(dwABPOS["ABPSOLLMENGE7"].ToString()) * 0.01;
                    value = dwABPOS["ABPLIEFDAT"].ToString();
                    x = 4; y = 30;
                    exAgent.SetCellValue(x, y, value);
                    var query = from rows in dsSource.Tables["FSSTAMM"].AsEnumerable()
                                where rows["FSART"].ToString() == art
                                & rows["FSVAR"].ToString() == var
                                select rows;
                    foreach (DataRow rw in query)
                    {
                        x = 20; y = 1;
                        exAgent.SetCellValue(x, y, art);
                        x = 20; y = 6;
                        exAgent.SetCellValue(x, y, var);
                        value = "品名:"+GetGB2312Value(rw["FSABEZ"].ToString());
                        x = 21; y = 1;
                        exAgent.SetCellValue(x, y, value);
                    }
                    #region====Color===================
                    int posx = 20;
                    int posy = 16;
                    if (!processed.Contains(color + ";"))
                    {
                        processed += color + ";";
                        rown++;
                        x = 20 + rown * 2; y = 14;
                        exAgent.SetCellValue(x, y, color);                        
                        query = from rows in dsSource.Tables["FSFARBEN"].AsEnumerable()
                                where rows["FSFART"].ToString() == art
                                & rows["FSFVAR"].ToString() == var
                               & rows["FSFFARBE"].ToString() == color
                                select rows;
                        foreach (DataRow rw in query)     
                        {                            
                            value =GetGB2312Value( rw["FSFFBEZ"].ToString());
                            x = 20 + rown * 2+1; y = 14;
                            exAgent.SetCellValue(x, y, value);
                        }
                        colorpos.Add(color, 20 + rown * 2);
                        posx = 20 + rown * 2;
                    }
                    else
                    {
                        posx = colorpos[color];
                    }

                    switch (size.Trim())
                    {
                        case "S":
                            posy = 24;
                            break;
                        case "M":
                            posy = 27;
                            break;
                        case "L":
                            posy = 30;
                            break;
                        case "XL":
                            posy = 33;
                            break;
                    }
                    exAgent.SetCellValue(posx, posy, qty);

                    #endregion

                    #region====price=======
                    query = from rows in dsSource.Tables["ABPR"].AsEnumerable()
                            where rows["ARAUFKRS"].ToString() == dwABPOS["ABPAUFKRS"].ToString()
                            & rows["ARAUFLFD"].ToString() == dwABPOS["ABPAUFLFD"].ToString()
                            & rows["ARAUFPOS"].ToString() == dwABPOS["ABPAUFPOS"].ToString()
                            select rows;
                    foreach (DataRow rw in query)
                    {
                        value = rw["ARWERT"].ToString();
                        value = System.Math.Round(System.Convert.ToDouble(value) * 0.001, 3).ToString() + "";
                        exAgent.SetCellValue(6, 23, value);
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInWDECK( int startx, int starty)
        {
            try
            {
                DataTable tb = dsSource.Tables["WDECK"];
                var query =( from rw in tb.AsEnumerable()
                            group rw by new
                            {
                                ART = rw["WDART"],
                                VAR = rw["WDVAR"],
                                COLOR = rw["WDFARBE"],
                            }
                                into g
                                select new
                                {
                                    KEY = g.Key,
                                    QTY = g.Sum(rw => rw.Field<decimal>("WDBEDARF")),
                                }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.COLOR, new CaseInsensitiveComparer()); 
                int rowd =-1;
                int x = startx;
                int y = starty;
                foreach (var rw in query)
                {
                    rowd++;
                    string art=rw.KEY.ART.ToString() ;
                    string var= Left(rw.KEY.VAR.ToString() + "   ", 2);
                    string color= rw.KEY.COLOR.ToString();
                    exAgent.SetCellValue(x + rowd*2, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    var qw = from rwx in dsSource.Tables["WASTAMM"].AsEnumerable()
                             where rwx["WAART"].ToString() ==art
                             && rwx["WAVAR"].ToString() ==var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 1, GetGB2312Value(rwx["WABEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd*2, 14, rw.KEY.COLOR);
                    var qwF = from rwx in dsSource.Tables["WAFARBEN"].AsEnumerable()
                              where rwx["WAFART"].ToString() == art
                             && rwx["WAFVAR"].ToString() ==var
                             && rwx["WAFZUSATZ"].ToString() == color
                             select rwx;
                    foreach (DataRow rwx in qwF)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 14, GetGB2312Value(rwx["WAFBEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd*2, 24, System.Convert.ToDouble(rw.QTY) * 0.01);
                    string filter = "ABAART='" + art + "' AND ABAFARBE='" + color + "'";
                    DataRow[] rows = dsSource.Tables["ICC_ABAUFM"].Select(filter);
                    string ek = "";
                    string processed = "";
                    foreach (DataRow row in rows)
                    { 
                        exAgent.SetCellValue(x + rowd * 2, 20, row["RATIO"]);
                        ek = row["ABAAUFKRS"].ToString()
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFLFD"].ToString())
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFPOS"].ToString());
                        if (!processed.Contains(ek + ";"))
                            processed += ek + ";";
                    }
                    exAgent.SetCellValue(x + rowd * 2, 30, processed);
                } 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInFDECK( int startx, int starty)
        {
            try
            {
                DataTable tb = dsSource.Tables["FDECK"];
                var query =( from rw in tb.AsEnumerable()
                            where rw["FDTYP"].ToString() == "B"
                            group rw by new
                            {
                                ART = rw["FDART"],
                                VAR = rw["FDVAR"],
                                COLOR = rw["FDFARBE"],
                                SIZE = rw["FDGROE"],
                            }
                                into g
                                select new
                                {
                                    KEY = g.Key,
                                    QTY = g.Sum(rw => rw.Field<decimal>("FDMENGE")),
                                }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                  .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer())
                                  .ThenBy(b => b.KEY.COLOR, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.SIZE, new CaseInsensitiveComparer()); 
                int rowd = -1;
                int x = startx;
                int y = starty;
                foreach (var rw in query)
                {
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 2);
                    string color = rw.KEY.COLOR.ToString();
                    string size = rw.KEY.SIZE.ToString();
                    rowd++;
                    exAgent.SetCellValue(x + rowd*2, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    var qw = from rwx in dsSource.Tables["FSSTAMM"].AsEnumerable()
                             where rwx["FSART"].ToString() == art
                             && rwx["FSVAR"].ToString() == var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 1, GetGB2312Value(rwx["FSABEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd * 2, 14, rw.KEY.COLOR);
                    var qwF = from rwx in dsSource.Tables["FSFARBEN"].AsEnumerable()
                              where rwx["FSFART"].ToString() == art
                              && rwx["FSFVAR"].ToString() ==var
                              && rwx["FSFFARBE"].ToString() == color
                              select rwx;
                    foreach (DataRow rwx in qwF)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 14, GetGB2312Value(rwx["FSFFBEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd*2, 19, rw.KEY.SIZE);
                    exAgent.SetCellValue(x + rowd*2, 24, System.Convert.ToDouble(rw.QTY));
                    string filter = "ABAART='" + art + "' AND ABAFARBE='" + color + "' AND ABAGROE='"+size+"'";
                    DataRow[] rows = dsSource.Tables["ICC_ABAUFM"].Select(filter);
                    string ek = "";
                    string processed="";
                    foreach (DataRow row in rows)
                    {
                        exAgent.SetCellValue(x + rowd * 2, 23, row["RATIO"]);
                        ek = row["ABAAUFKRS"].ToString()
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFLFD"].ToString())
                            + "-" + Helpers.Helper.GetSubRollNum(row["ABAAUFPOS"].ToString());
                        if (!processed.Contains(ek + ";"))
                            processed += ek + ";";
                    }
                    exAgent.SetCellValue(x + rowd * 2, 30, processed);
                }              
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInMDECK( int startx, int starty)
        {
            try
            {
                DataTable tb = dsSource.Tables["MDECK"];
                var query =( from rw in tb.AsEnumerable()
                            group rw by new
                            {
                                ART = rw["MDMAT"],
                                VAR = rw["MDVAR"],
                            }
                                into g
                                select new
                                {
                                    KEY = g.Key,
                                    QTY = g.Sum(rw => rw.Field<decimal>("MDMENGE")),
                                }).OrderBy(b => b.KEY.ART, new CaseInsensitiveComparer())
                                .ThenBy(b => b.KEY.VAR, new CaseInsensitiveComparer()); 
                int rowd = -1;
                int x = startx;
                int y = starty;
                foreach (var rw in query)
                {
                    rowd++;
                    string art = rw.KEY.ART.ToString();
                    string var = Left(rw.KEY.VAR.ToString() + "   ", 3);
                    exAgent.SetCellValue(x + rowd*2, 1, rw.KEY.ART.ToString() + rw.KEY.VAR.ToString());
                    var qw = from rwx in dsSource.Tables["MSSTAMM"].AsEnumerable()
                             where rwx["MSMAT"].ToString() == art
                             && rwx["MSVAR"].ToString() == var
                             select rwx;
                    foreach (DataRow rwx in qw)
                    {
                        exAgent.SetCellValue(x + rowd * 2 + 1, 1, GetGB2312Value(rwx["MSBEZ"].ToString()));
                    }
                    exAgent.SetCellValue(x + rowd*2, 24, System.Convert.ToDouble(rw.QTY));
                }
       
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        public DataSet CreateMatrix(DataTable tbABPOS)
        {
            DataSet Return = new DataSet();

            DataTable tbSizeColorMatrix = new DataTable("SizeColorMatrix");
            tbSizeColorMatrix.Columns.Add(new DataColumn("ARTICLE"));
            tbSizeColorMatrix.Columns.Add(new DataColumn("VAR"));
            tbSizeColorMatrix.Columns.Add(new DataColumn("COLOR"));

            Dictionary<string, int> colors = new Dictionary<string, int>();
            foreach (DataRow row in tbABPOS.Rows)
            {
                string color = row["ABPFARBE"].ToString();
                string size = row["ABPGROE"].ToString().Trim();
                double qty = System.Convert.ToDouble(row["ABPSOLLMENGE7"].ToString());
                bool added = false;
                bool newcolor = false;
                int yc = tbSizeColorMatrix.Columns.Count;
                int y = 0;
                added = false;
                DataRow rmx = null;
                var query = from DataRow rm in tbSizeColorMatrix.AsEnumerable()
                            where rm["颜色"].ToString() == color
                            select rm;
                foreach (DataRow rm in query)
                {
                    rmx = rm;
                }
                if (rmx == null)
                {
                    rmx = tbSizeColorMatrix.NewRow();
                    newcolor = true;
                }
                rmx["颜色"] = color;

                added = false;
                foreach (DataColumn m in tbSizeColorMatrix.Columns)
                {
                    if (m.ColumnName == size)
                    {
                        added = true;
                        break;
                    }
                    y++;
                }
                if (!added)//未找到列；
                {
                    tbSizeColorMatrix.Columns.Add(new DataColumn(size));
                }
                yc = tbSizeColorMatrix.Columns.Count;
                rmx[y] = qty;
                if (newcolor)
                {
                    tbSizeColorMatrix.Rows.Add(rmx);
                }
            }

            Return.Merge(tbSizeColorMatrix);
            return Return;
        }
        private void HideNullRows(int pages)
        {
            try
            {
                exAgent.HideRows((pages + 1) * 25 + 1, 1, 500, 999);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = "";

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "AB05DOC";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\YCA\YCAAB05DOC.xls");
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                string newfilename = orderno;
                exAgent.CreateNewSheet(newfilename, Standard, orderno.Replace(".xls", ""), pwd);
                filename = exAgent.FileName;
                FillInEXCEL();
                int startx = 53, starty = 1;
                FillInWDECK(startx, starty);
                startx = 83;
                FillInMDECK( startx, starty);
                startx = 163;
                FillInFDECK( startx, starty);
                string localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        private void LocalFile(string ordernumer)
        {
            DataRow dwABKOPF = dsSource.Tables["ABKOPF"].Rows[0];
            string fileabsname = ordernumer + ".xls";
            string localFile = @"C:\Windows\Temp\" + fileabsname;
            string tempfile = "";
            try
            {
                tempfile = CreateFile(fileabsname);
                localFile = @"C:\IntexReport\生产单\" + fileabsname;
                if (System.IO.File.Exists(localFile))
                {
                    System.IO.File.Delete(localFile);
                }
                File.Move(tempfile, localFile);
                System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}